package com.siemens.industrial.web.mvc.dao.partner;

import com.siemens.industrial.model.partner.User;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Types;
import java.util.List;
import java.util.Map;

/**
 * Created by qupen on 2016/8/31.
 */
@Service
public class PartnerUserDao {
    @Resource
    JdbcTemplate jdbcTemplate;

    private static final String TENANT_KEY = "{tenant_key}";
    private static final String USER_ITEMS_STRING = "'"+ TENANT_KEY + "' tenant_key,user_id,user_name, user_pwd, role_id, enabled, contact, telephone, email, department, job, `group`, logo_md5, dashboard_config, create_time, update_time,first_login ";

    private static final String databaseTag = "{database}";
    private static final String getUserQuery = "SELECT "+ USER_ITEMS_STRING +" FROM `" + databaseTag + "`.`user` WHERE `user_id` = ?";
    private static final String getUserByNameQuery = "SELECT "+ USER_ITEMS_STRING + " FROM `" + databaseTag + "`.`user` WHERE `user_name` = ? AND `enabled` = 1";
    private static final String getDisabledUserQuery = "SELECT "+ USER_ITEMS_STRING +" FROM `" + databaseTag + "`.`user` WHERE `user_id` = ? AND `enabled` = 0";
    private static final String getUsersQuery = "SELECT "+ USER_ITEMS_STRING + " FROM `" + databaseTag + "`.`user`";
    private static final String saveUserQuery = "INSERT INTO `" + databaseTag + "`.`user` (`user_id`, `user_name`, `user_pwd`, `role_id`, `contact`, `telephone`, `email`, `department`, `job`, `group`, `logo_md5`, `dashboard_config`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String updateUserQuery = "UPDATE `" + databaseTag + "`.`user` SET `user_name` = ?, `contact` = ?, `telephone` = ?, `email` = ?, `department` = ?, `job` = ?, `group` = ?, `logo_md5` = ? WHERE `user_id` = ?";
    private static final String updateUserPwdQuery = "UPDATE `" + databaseTag + "`.`user` SET `user_pwd` = ? WHERE `user_id` = ?";
    private static final String updateUserFirstLogin = "UPDATE `"+databaseTag+"`.`user` SET `first_login`='1' WHERE `user_id`= ?;";
    private static final String deleteUserQuery = "UPDATE `" + databaseTag + "`.`user` SET `enabled` = 0 WHERE `user_id` = ?";
    private static final String recoveryUserQuery = "UPDATE `" + databaseTag + "`.`user` SET `enabled` = 1 WHERE `user_id` = ?";
    private static final String updateTenantUserEnabled = "UPDATE `" + databaseTag + "`.`user` SET `enabled` = ? WHERE `user_id` = ?";
    private static final String getUserGroup = "SELECT DISTINCT(`group`) FROM `" + databaseTag + "`.`user`";
    private static final String getTenantService="SELECT \n" +
            "    a.service_name,\n" +
            "    a.service_desc,\n" +
            "    c.enabled,\n" +
            "    concat(c.start_date,' - ',c.end_date) `date`\n" +
            "    FROM\n" +
            "    siemens.service a\n" +
            "        LEFT JOIN\n" +
            "    (SELECT \n" +
            "        *\n" +
            "    FROM\n" +
            "        siemens.tenant_service_relation b\n" +
            "    WHERE\n" +
            "        b.tenant_id = ?) c ON a.service_id = c.service_id;";
    public User getUser(String tenantKey, String database, String userId) {
        try {
            return jdbcTemplate.queryForObject(getUserQuery.replace(TENANT_KEY, tenantKey).replace(databaseTag, database), new Object[]{userId}, new int[]{Types.VARCHAR}, User.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public User getUserByName(String tenantKey, String database, String userName) {
        try {
            return jdbcTemplate.queryForObject(getUserByNameQuery.replace(TENANT_KEY, tenantKey).replace(TENANT_KEY, tenantKey).replace(databaseTag, database), new Object[]{userName}, new int[]{Types.VARCHAR}, User.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public User getDisabledUser(String tenantKey, String database, String userId) {
        try {
            return jdbcTemplate.queryForObject(getDisabledUserQuery.replace(TENANT_KEY, tenantKey).replace(databaseTag, database), new Object[]{userId}, new int[]{Types.VARCHAR}, User.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<User> getUsers(String tenantKey, String database) {
        return jdbcTemplate.query(getUsersQuery.replace(TENANT_KEY, tenantKey).replace(databaseTag, database), User.getRowMapper());
    }

    public void updateUser(String database, User user) {
        jdbcTemplate.update(updateUserQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, user.getUserName());
                    pss.setString(2, user.getContact());
                    pss.setString(3, user.getTelephone());
                    pss.setString(4, user.getEmail());
                    pss.setString(5, user.getDepartment());
                    pss.setString(6, user.getJob());
                    pss.setString(7, user.getGroup());
                    pss.setString(8, user.getLogoMd5());
                    pss.setString(9, user.getUserId());
                }
        );
    }

    public int updateUserPwd(String database, String userId, String password) {
       return jdbcTemplate.update(updateUserPwdQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, password);
                    pss.setString(2, userId);
                }
        );
    }

    public int saveUser(String database, User user) {
       return jdbcTemplate.update(saveUserQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, user.getUserId());
                    pss.setString(2, user.getUserName());
                    pss.setString(3, user.getPassword());
                    pss.setInt(4, user.getRoleId());
                    pss.setString(5, user.getContact());
                    pss.setString(6, user.getTelephone());
                    pss.setString(7, user.getEmail());
                    pss.setString(8, user.getDepartment());
                    pss.setString(9, user.getJob());
                    pss.setString(10, user.getGroup());
                    pss.setString(11, user.getLogoMd5());
                    pss.setString(12, user.getDashboardConfig());
                }
        );
    }

    public void deleteUser(String database, String userId) {
        jdbcTemplate.update(deleteUserQuery.replace(databaseTag, database), new Object[]{userId}, new int[]{Types.VARCHAR});
    }

    public void updateTenantUserEnabled(String database, int enabled, String userId) {
        jdbcTemplate.update(updateTenantUserEnabled.replace(databaseTag, database), new Object[]{enabled, userId});
    }

    public void recoveryUser(String database, String userId) {
        jdbcTemplate.update(recoveryUserQuery.replace(databaseTag, database), new Object[]{userId}, new int[]{Types.VARCHAR});
    }

    public List<Map<String,Object>> getUserGroup(String database) {
     return jdbcTemplate.queryForList(getUserGroup.replace(databaseTag, database));
    }

    public List<Map<String,Object>> getTenantService(String tenantId){
        return jdbcTemplate.queryForList(getTenantService,tenantId);
    }

    @Transactional
    public void updateUserFirstLogin(String database, String userId) {
        jdbcTemplate.update(updateUserFirstLogin.replace(databaseTag, database),userId);
    }
}
